# -*- coding: utf-8 -*-
"""
数据库表记录操作：插入/更新/删除
"""
import logging
import uuid
from datetime import date

from sqlalchemy.dialects.mysql import insert
from sqlalchemy.orm import Session

from py_engine import _Session
from py_table import User

logger = logging.getLogger(__name__)


def bulk_save():
    session = _Session()

    # user_list = list()
    # for i in range(100000):
    #     user_list.append(User(user_name=uuid.uuid4().hex, update_column=float(i), user_birthday=date.today()))
    #     pass
    #
    # logger.info('start')
    # # session.add_all(user_list)
    # # 批量保存
    # session.bulk_save_objects(user_list)

    user_id = 330015
    # user = User(user_name='chench12345d', update_column=0.1, user_birthday=date.today())
    user_dict = {
        'user_name': uuid.uuid4().hex, 'update_column': 0.1, 'user_birthday': date.today(),
    }
    # user = User(**user_dict)
    # user.user_id = user_id
    # user_name = session.query(User.user_name).filter(User.user_id == user_id).first()
    user = session.query(User).get(user_id)
    # logger.info(f'user_name:{user_name}')
    # session.add(user)
    # logger.info(f'auto increment:{user.user_id}')
    # user = session.merge(user)
    # logger.info(f'user.user_name:{user.user_name}')
    # session.flush()  # 写入数据库内存：刷新插入数据，返回自增主键
    # logger.info(f'auto increment:{user.user_id}')
    # session.commit()  # 写入数据库磁盘
    # logger.info(f'auto increment:{user.user_id}')
    # session.refresh(user)  # 从数据库更新数据
    # logger.info(f'auto increment:{user.user_id}')
    session.expunge(user)  # 断开数据库关联
    session.close()
    logger.info('session.close')
    logger.info(f'auto increment:{user.user_id}')
    user.user_name = reversed(user.user_name)
    logger.info(f'user.user_name:{user.user_name}')

    logger.info('stop')


def bulk_insert_or_update():
    """批量更新或插入"""

    session = _Session()
    data = session.query(User).all()
    insert_list = list()
    update_list = list()

    for item in data:
        # insert_list.append({
        #     'user_name': item.user_name,
        # })
        update_list.append({
            'user_id': item.user_id,
            'user_name': item.user_name + 'q',
        })
        pass

    # insert_list.append({
    #     'user_name': 'chchen'
    # })
    # 参数区别在于字典参数是否包含主键
    insert_result = session.bulk_insert_mappings(User, insert_list)
    logger.info(f'insert_length={len(insert_list)}')

    update_result = session.bulk_update_mappings(User, update_list)
    logger.info(f'update_length={len(update_list)}')

    session.commit()
    session.close()


def batch_save_for_mysql():
    """批量保存：不存在就插入，存在就更新"""

    session = Session()
    insert_list = list()

    user = session.query(User).filter(User.user_name == 'chench').first()
    if user:
        insert_list.append({
            'user_id': user.user_id,
            'user_name': user.user_name,
            'update_column': 1.6
        })
        pass

    insert_list.append({
        'user_id': None,  # 插入主键必须置空，不能没有
        'user_name': 'chchen',
        'update_column': 1.4
    })

    update_column_list = ['update_column']
    insert_statement = insert(User).values(insert_list)
    update_column_dict = {
        column.name: column
        for column in insert_statement.inserted
        if column.name in update_column_list
    }
    update_statement = insert_statement.on_duplicate_key_update(**update_column_dict)

    result = session.execute(update_statement)
    logger.info(f'result_length={result.rowcount}')

    session.commit()
    session.close()


if __name__ == '__main__':
    # bulk_save()
    bulk_insert_or_update()
    # orm_session()

    pass
